import 'package:forme/models/git_tree_item.dart';
import 'package:logger/logger.dart';
import 'package:shared_preferences/shared_preferences.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'dart:io';
import 'dart:convert';
import '../models/note.dart';
import '../models/todo.dart';
import 'api_service.dart';
import 'package:flutter/services.dart' show rootBundle;

class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  static Database? _database;
  final ApiService _apiService = ApiService();

  factory DatabaseService() => _instance;

  DatabaseService._internal();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), 'notes.db');
    // 检查数据库文件是否存在
    final dbFile = File(path);
    // 从Git仓库中获取数据库文件
    GitTreeItem? noteDb = await _getDatabaseFileFromGit();
    // 如果仓库中没有数据库文件 则上传本地数据库文件到Git仓库
    if (noteDb == null) {
      final db = await openDatabase(
        path,
        version: 1,
        onCreate: _createDb,
      );
      // 创建数据库后，将文件上传到Git仓库
      await _uploadDatabaseFile(path);
      return db;
    }
    if (!await dbFile.exists()) {
      try {
        await _downloadDatabaseFile(noteDb);
      } catch (e) {
        // 如果下载失败，创建新的数据库
        final db = await openDatabase(
          path,
          version: 1,
          onCreate: _createDb,
        );

        // 创建数据库后，将文件上传到Git仓库
        await _uploadDatabaseFile(path);

        return db;
      }
    }

    return await openDatabase(
      path,
      version: 1,
      onCreate: _createDb,
    );
  }

  /// 上传数据库文件
  Future<void> _uploadDatabaseFile(String dbPath) async {
    try {
      // 读取数据库文件内容
      final file = File(dbPath);
      final bytes = await file.readAsBytes();
      final content = base64Encode(bytes);
      Logger().i(content);
      // 上传到Git仓库
      await _apiService.uploadFile(
        repo: 'notedb',
        path: 'notes.db',
        content: content,
        message: '更新数据库文件',
      );
    } catch (e) {
      throw Exception('上传数据库文件失败: $e');
    }
  }

  /// 下载数据库文件
  Future<void> _downloadDatabaseFile(GitTreeItem noteDb) async {
    try {
      // 从Git仓库获取数据库文件内容
      final blob = await _apiService.getFileContent(
        repo: 'notedb', // 替换为实际的仓库名
        sha: noteDb.sha, // 替换为实际的文件SHA
      );

      if (blob.encoding == 'base64') {
        // 解码base64内容
        final bytes = base64.decode(blob.content);

        // 获取数据库文件路径
        final dbPath = join(await getDatabasesPath(), 'notes.db');

        // 写入文件
        await File(dbPath).writeAsBytes(bytes);
      } else {
        throw Exception('不支持的编码格式: ${blob.encoding}');
      }
    } catch (e) {
      throw Exception('下载数据库文件失败: $e');
    }
  }

  Future<void> _createDb(Database db, int version) async {
    await db.execute('''
      CREATE TABLE notes(
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        createdAt TEXT NOT NULL,
        modifiedAt TEXT NOT NULL
      )
    ''');

    await db.execute('''
      CREATE TABLE todos(
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT,
        isCompleted INTEGER NOT NULL DEFAULT 0,
        priority INTEGER NOT NULL DEFAULT 2,
        createdAt TEXT NOT NULL,
        modifiedAt TEXT NOT NULL,
        dueDate TEXT,
        category TEXT
      )
    ''');

    await db.execute('''
      CREATE TABLE sport_records(
        id TEXT PRIMARY KEY,
        date TEXT NOT NULL,
        type TEXT NOT NULL,
        count INTEGER DEFAULT 0,
        duration INTEGER DEFAULT 0,
        createdAt TEXT NOT NULL
      )
    ''');
  }

  // 保存便笺
  Future<void> saveNote(Note note) async {
    final db = await database;
    await db.insert(
      'notes',
      note.toJson(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  // 获取所有便笺
  Future<List<Note>> getAllNotes() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('notes');
    return List.generate(maps.length, (i) => Note.fromJson(maps[i]));
  }

  // 更新便笺
  Future<void> updateNote(Note note) async {
    final db = await database;
    await db.update(
      'notes',
      note.toJson(),
      where: 'id = ?',
      whereArgs: [note.id],
    );
    markDatabaseModified(true);
  }

  // 删除便笺
  Future<void> deleteNote(String id) async {
    final db = await database;
    await db.delete(
      'notes',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  // 搜索便笺
  Future<List<Note>> searchNotes(String query) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'notes',
      where: 'title LIKE ? OR content LIKE ?',
      whereArgs: ['%$query%', '%$query%'],
    );
    return List.generate(maps.length, (i) => Note.fromJson(maps[i]));
  }

  // 检查数据库文件是否存在
  Future<File> getDbFile() async {
    final dbPath = join(await getDatabasesPath(), 'notes.db');
    return File(dbPath);
  }

  Future<GitTreeItem?> _getDatabaseFileFromGit() async {
    final fileItems = await _apiService.getRepoTree(
      repo: 'notedb', // 替换为实际的仓库名
      sha: 'master', // 替换为实际的分支名
    );

    return fileItems.cast<GitTreeItem?>().firstWhere(
          (item) => item?.path == 'notes.db',
          orElse: () => null,
        );
  }

  /// 使用SharedPreferences记录数据库有更改
  Future<void> markDatabaseModified(bool modified) async {
    final _prefs = await SharedPreferences.getInstance();
    await _prefs.setBool('database_changed', modified);
  }

  /// 检查是否需要同步
  Future<bool> shouldSyncDatabase() async {
    final prefs = await SharedPreferences.getInstance();
    return prefs.getBool('database_changed') ?? false;
  }

  ///  同步数据库
  Future<String> syncDatabase() async {
    if (await shouldSyncDatabase()) {
      final gitTreeItem = await _getDatabaseFileFromGit();
      if (gitTreeItem == null) {
        return '远程仓库中未找到数据库文件。';
      }
      File dbFile = await getDbFile();
      if (await dbFile.exists()) {
        try {
          final bytes = await dbFile.readAsBytes();
          final content = base64Encode(bytes);
          final newSha = await _apiService.updateFile(
            repo: 'notedb',
            sha: gitTreeItem.sha,
            path: 'notes.db',
            content: content,
            message: '更新数据库文件',
          );
          final prefs = await SharedPreferences.getInstance();
          await prefs.setString('note_db_sha', newSha);
          await prefs.setBool('database_changed', false); // 重置标志
          return '数据库同步成功';
        } catch (e) {
          return '数据库同步失败: $e';
        }
      } else {
        return '本地数据库文件不存在';
      }
    } else {
      return '数据库已是最新，无需同步';
    }
  }

  Future<String?> getNoteDbSha() async {
    final prefs = await SharedPreferences.getInstance();
    return prefs.getString('note_db_sha');
  }

  Future<bool> refreshNoteDb() async {
    GitTreeItem? noteDb = await _getDatabaseFileFromGit();
    if (noteDb != null) {
      var currentSha = noteDb.sha;
      final prefs = await SharedPreferences.getInstance();
      final currentShaPrefs = prefs.getString('note_db_sha');
      Logger().i('当前SHA: $currentSha vs $currentShaPrefs');
      if (currentShaPrefs != currentSha) {
        // 如果当前SHA和保存的SHA不一致，则下载数据库文件
        await _downloadDatabaseFile(noteDb);
        // 更新当前SHA
        await prefs.setString('note_db_sha', currentSha);
        return true;
      }
      return false;
    } else {
      return false;
    }
  }

  // 待办事项相关方法

  // 保存待办事项
  Future<void> saveTodo(Todo todo) async {
    final db = await database;
    await db.insert(
      'todos',
      todo.toJson(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
    markDatabaseModified(true);
  }

  // 获取所有待办事项
  Future<List<Todo>> getAllTodos() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'todos',
      orderBy: 'priority ASC, createdAt DESC',
    );
    return List.generate(maps.length, (i) => Todo.fromJson(maps[i]));
  }

  // 根据完成状态获取待办事项
  Future<List<Todo>> getTodosByStatus(bool isCompleted) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'todos',
      where: 'isCompleted = ?',
      whereArgs: [isCompleted ? 1 : 0],
      orderBy: 'priority ASC, createdAt DESC',
    );
    return List.generate(maps.length, (i) => Todo.fromJson(maps[i]));
  }

  // 根据分类获取待办事项
  Future<List<Todo>> getTodosByCategory(String category) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'todos',
      where: 'category = ?',
      whereArgs: [category],
      orderBy: 'priority ASC, createdAt DESC',
    );
    return List.generate(maps.length, (i) => Todo.fromJson(maps[i]));
  }

  // 更新待办事项
  Future<void> updateTodo(Todo todo) async {
    final db = await database;
    await db.update(
      'todos',
      todo.toJson(),
      where: 'id = ?',
      whereArgs: [todo.id],
    );
    markDatabaseModified(true);
  }

  // 删除待办事项
  Future<void> deleteTodo(String id) async {
    final db = await database;
    await db.delete(
      'todos',
      where: 'id = ?',
      whereArgs: [id],
    );
    markDatabaseModified(true);
  }

  // 搜索待办事项
  Future<List<Todo>> searchTodos(String query) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'todos',
      where: 'title LIKE ? OR description LIKE ?',
      whereArgs: ['%$query%', '%$query%'],
      orderBy: 'priority ASC, createdAt DESC',
    );
    return List.generate(maps.length, (i) => Todo.fromJson(maps[i]));
  }

  // 切换待办事项完成状态
  Future<void> toggleTodoComplete(String id) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'todos',
      where: 'id = ?',
      whereArgs: [id],
    );

    if (maps.isNotEmpty) {
      final todo = Todo.fromJson(maps.first);
      final updatedTodo = todo.copyWith(isCompleted: !todo.isCompleted);
      await updateTodo(updatedTodo);
    }
  }

  // 获取待办事项统计信息
  Future<Map<String, int>> getTodoStats() async {
    final db = await database;
    final totalResult =
        await db.rawQuery('SELECT COUNT(*) as count FROM todos');
    final completedResult = await db
        .rawQuery('SELECT COUNT(*) as count FROM todos WHERE isCompleted = 1');
    final pendingResult = await db
        .rawQuery('SELECT COUNT(*) as count FROM todos WHERE isCompleted = 0');

    return {
      'total': totalResult.first['count'] as int,
      'completed': completedResult.first['count'] as int,
      'pending': pendingResult.first['count'] as int,
    };
  }

  // 保存运动记录
  Future<void> saveSportRecord({
    required String id,
    required String date,
    required String type,
    required int count,
    required int duration,
  }) async {
    final db = await database;
    // 检查当天同类型是否已存在
    final List<Map<String, dynamic>> exist = await db.query(
      'sport_records',
      where: 'date = ? AND type = ?',
      whereArgs: [date, type],
      limit: 1,
    );
    if (exist.isNotEmpty) {
      // 更新
      await db.update(
        'sport_records',
        {
          'count': count,
          'duration': duration,
          'createdAt': DateTime.now().toIso8601String(),
        },
        where: 'date = ? AND type = ?',
        whereArgs: [date, type],
      );
    } else {
      // 插入
      await db.insert(
        'sport_records',
        {
          'id': id,
          'date': date,
          'type': type,
          'count': count,
          'duration': duration,
          'createdAt': DateTime.now().toIso8601String(),
        },
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    }
    markDatabaseModified(true);
  }

  // 查询当天运动记录
  Future<Map<String, dynamic>?> getSportRecordByDate(
      String date, String type) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'sport_records',
      where: 'date = ? AND type = ?',
      whereArgs: [date, type],
      limit: 1,
    );
    if (maps.isNotEmpty) return maps.first;
    return null;
  }

  // 查询今日之前最近一次指定类型的运动记录
  Future<Map<String, dynamic>?> getLastSportRecordBeforeToday(
      String type) async {
    final db = await database;
    final today = DateTime.now().toIso8601String().substring(0, 10);
    final List<Map<String, dynamic>> maps = await db.query(
      'sport_records',
      where: 'type = ? AND date < ?',
      whereArgs: [type, today],
      orderBy: 'date DESC',
      limit: 1,
    );
    if (maps.isNotEmpty) return maps.first;
    return null;
  }

  // region 双色球历史库
  static Database? _lotteryDb;

  static Future<Database> get lotteryDatabase async {
    if (_lotteryDb != null) return _lotteryDb!;
    _lotteryDb = await _initLotteryDb();
    return _lotteryDb!;
  }

  static Future<Database> _initLotteryDb() async {
    // 复制 assets/lottery_data.db 到本地
    final databasesPath = await getDatabasesPath();
    final path = join(databasesPath, 'lottery_data.db');
    if (!await File(path).exists()) {
      // 复制
      final data = await rootBundle.load('assets/lottery_data.db');
      final bytes =
          data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
      await File(path).writeAsBytes(bytes, flush: true);
    }
    return await openDatabase(path, readOnly: true);
  }

  static Future<List<Map<String, dynamic>>> getAllLotteryRows() async {
    final db = await lotteryDatabase;
    return await db.query('lottery');
  }
  // endregion
}
